#include"student_manage.h"

// 回调函数，用于显示查询结果
static int callback(void *NotUsed, int argc, char **argv, char **azColName) {
    for (int i = 0; i < argc; i++) {
        printf("%s = %s\t", azColName[i], argv[i] ? argv[i] : "NULL");
    }
    printf("\n");
    return 0;
}

// 初始化数据库
void initDB(sqlite3 **db) {
    char *errMsg = 0;
    int rc = sqlite3_open("students.db", db);

    if (rc) {
        fprintf(stderr, "无法打开数据库: %s\n", sqlite3_errmsg(*db));
        exit(0);
    } else {
        fprintf(stdout, "成功打开数据库\n");
    }

    // 创建学生表（如果不存在）
    const char *createTableSQL = "CREATE TABLE IF NOT EXISTS students ("
                                "id TEXT PRIMARY KEY,"
                                "name TEXT NOT NULL,"
                                "class TEXT NOT NULL);";

    rc = sqlite3_exec(*db, createTableSQL, callback, 0, &errMsg);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "SQL错误: %s\n", errMsg);
        sqlite3_free(errMsg);
    } else {
        fprintf(stdout, "表创建成功或已存在\n");
    }
}

// 录入学生信息
void addStudent(sqlite3 *db) {
    char id[50], name[50], class[50];
    char sql[200];
    char *errMsg = 0;

    printf("请输入学生学号：");
    scanf("%s", id);
    printf("请输入学生姓名：");
    scanf("%s", name);
    printf("请输入学生班级：");
    scanf("%s", class);

    // 构建SQL插入语句
    snprintf(sql, sizeof(sql),"INSERT INTO students (id, name, class) "
                 "VALUES ('%s', '%s', '%s');", id, name, class);

    int rc = sqlite3_exec(db, sql, callback, 0, &errMsg);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "SQL错误: %s\n", errMsg);
        sqlite3_free(errMsg);
    } else {
        fprintf(stdout, "学生信息录入成功\n");
    }
}

// 更新学生信息
void updateStudent(sqlite3 *db) {
    char id[50], name[50], class[50];
    char sql[200];
    char *errMsg = 0;

    printf("请输入要更新的学生学号：");
    scanf("%s", id);

    // 先检查学生是否存在
    sprintf(sql, "SELECT * FROM students WHERE id='%s';", id);
    int rc = sqlite3_exec(db, sql, callback, 0, &errMsg);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "SQL错误: %s\n", errMsg);
        sqlite3_free(errMsg);
        return;
    }

    printf("请输入新的学生姓名：");
    scanf("%s", name);
    printf("请输入新的学生班级：");
    scanf("%s", class);

    // 构建SQL更新语句
    snprintf(sql,sizeof(sql), "UPDATE students SET name='%s', class='%s' WHERE id='%s';", name, class, id);
    rc = sqlite3_exec(db, sql, callback, 0, &errMsg);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "SQL错误: %s\n", errMsg);
        sqlite3_free(errMsg);
    } else {
        fprintf(stdout, "学生信息更新成功\n");
    }
}

// 删除学生信息
void deleteStudent(sqlite3 *db) {
    char id[50];
    char sql[100];
    char *errMsg = 0;

    printf("请输入要删除的学生学号：");
    scanf("%s", id);

    // 构建SQL删除语句
    snprintf(sql, sizeof(sql),"DELETE FROM students WHERE id='%s';", id);
    int rc = sqlite3_exec(db, sql, callback, 0, &errMsg);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "SQL错误: %s\n", errMsg);
        sqlite3_free(errMsg);
    } else {
        fprintf(stdout, "学生信息删除成功\n");
    }
}

// 显示所有学生信息
void displayAllStudents(sqlite3 *db) {
    const char *sql = "SELECT * FROM students;";
    char *errMsg = 0;

    printf("所有学生信息如下：\n");
    int rc = sqlite3_exec(db, sql, callback, 0, &errMsg);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "SQL错误: %s\n", errMsg);
        sqlite3_free(errMsg);
    }
}

int main() {
    sqlite3 *db;
    int choice;

    // 初始化数据库连接
    initDB(&db);

    do {
        printf("\n学生信息管理系统\n");
        printf("------------------------\n");
        printf("1. 录入学生信息\n");
        printf("2. 更新学生信息\n");
        printf("3. 删除学生信息\n");
        printf("4. 显示所有学生信息\n");
        printf("0. 退出系统\n");
        printf("------------------------\n");
        printf("请输入你的选择：");
        scanf("%d", &choice);

        switch (choice) {
            case 1:
                addStudent(db);
                break;
            case 2:
                updateStudent(db);
                break;
            case 3:
                deleteStudent(db);
                break;
            case 4:
                displayAllStudents(db);
                break;
            case 0:
                printf("感谢使用学生信息管理系统，再见！\n");
                break;
            default:
                printf("无效的选择，请重新输入！\n");
        }
    } while (choice != 0);

    // 关闭数据库连接
    sqlite3_close(db);
    return 0;
}    